BigQuery の INFORMATION_SCHEMA からどんな情報が取得できるのか、全ての VIEW を確認してみた
こんにちは、みかみです。
やりたいこと
BigQuery の INFORMATION_SCHEMA でどんな情報が参照できるのか知りたい
前提
BigQuery の INFORMATION_SCHEMA は、2020/04/10 現在、ベータ版とのことです。
INFORMATION_SCHEMA の View 一覧
2020/04 現在、BigQuery の INFORMATION_SCHEMA には、以下の View があります。
| 種別 | View | 概要 |
|---|---|---|
| データセット | SCHEMATA | データセット情報 |
| SCHEMATA_OPTIONS | 有効期限、ラベルなどのオプション情報 | |
| テーブル | TABLES | テーブル情報 |
| TABLE_OPTIONS | 有効期限、ラベルなどのオプション情報 | |
| COLUMNS | カラム情報 | |
| COLUMN_FIELD_PATHS | カラムの詳細情報 | |
| ビュー | VIEWS | ビュー情報 |
| ルーティン | ROUTINES | UDF、ストアドプロシージャ情報 |
| ROUTINE_OPTIONS | UDF、ストアドプロシージャのオプション情報 | |
| PARAMETERS | UDF、ストアドプロシージャのパラメータ情報 | |
| ジョブ | JOBS_BY_USER | ユーザーの実行ジョブ情報 |
| JOBS_BY_PROJECT | プロジェクトの実行ジョブ情報 | |
| JOBS_BY_ORGANIZATION | 組織の実行ジョブ情報 | |
| Reservations | RESERVATION_CHANGES_BY_PROJECT | Reservations 情報 |
| RESERVATION_TIMELINE_BY_PROJECT | Reservations のタイムライン情報 | |
| CAPACITY_COMMITMENT_CHANGES_BY_PROJECT | 容量コミットメントの変更情報 | |
| ASSIGNMENT_CHANGES_BY_PROJECT | 割り当ての変更情報 |
データセット情報
以下2つの View で、データセット関連の情報を取得できます。
- SCHEMATA：データセット情報
- SCHEMATA_OPTIONS：有効期限、ラベルなどのオプション情報
取得できるのは、プロジェクト内のユーザーがアクセス可能なデータセットの情報に限られます。
SCHEMATA
実際に SELECT 文を実行して、SCHEMATA のデータを参照してみます。
以下のデータセット情報が取得できました。
- CATALOG_NAME：プロジェクト名
- SCHEMA_NAME：データセット名
- CREATION_TIME：作成日時
- LAST_MODIFIED_TIME：最終更新日時
- LOCATION：ロケーション
View には SCHEMA_OWNER 項目もありますが、値は取得できず、ドキュメントによると現在のところ NULL 固定とのことです。
SCHEMATA_OPTIONS
続いて SCHEMATA_OPTIONS のデータを参照してみます。
あれ？ 何も返ってこない。。
ドキュメントによると、オプションの種類には以下の４つがあるようです。
- default_table_expiration_days：有効期間
- friendly_name：わかりやすいデータセット名
- description：データセットの説明
- labels：ラベル
オプション付きのデータセットがなかったので何も取得できなかったようです。。
それぞれのオプションをデータセットに付与しました。
なお、friendly_name は、管理画面 UI 操作では付与できないようなので、下記、Python コードからクライアントライブラリを使用して、friendly_name 付きのデータセットを作成して確認しました。
from google.cloud import bigquery
client = bigquery.Client()
dataset_id = "{}.test_dataset_friendly_name".format(client.project)
dataset = bigquery.Dataset(dataset_id)
dataset.location = "asia-northeast1"
dataset.friendly_name = "データセットの分かりやすい名前"
dataset = client.create_dataset(dataset)
print("Created dataset {}.{}".format(client.project, dataset.dataset_id))
再度、オプション情報取得用の SQL を実行してみます。
- CATALOG_NAME：プロジェクト名
- SCHEMA_NAME：データセット名
- OPTION_NAME：オプション名
- OPTION_TYPE：オプション値のデータ型
- OPTION_VALUE：オプション値
のオプション情報が取得できました。
テーブル情報
INFORMATION_SCHEMA の以下の View で、テーブル情報およびカラム情報を取得できます。
- TABLES：テーブル情報
- TABLE_OPTIONS：有効期限、ラベルなどのオプション情報
- COLUMNS：カラム情報
- COLUMN_FIELD_PATHS：カラムの詳細情報
TABLES、TABLE_OPTIONS
TABLES からテーブル情報を取得してみます。
以下の項目が取得できました。
- TABLE_CATALOG：プロジェクト名
- TABLE_SCHEMA：データセット名
- TABLE_NAME：テーブル名
- TABLE_TYPE：テーブルタイプ
- IS_INSERTABLE_INTO：INSERT 構文実行可能かどうか
- CREATION_TIME：テーブル作成日時
View には IS_TYPED カラムもありますが、現状 NO 固定とのことで、今後何らかの拡張が入りそうです。
TABLE_TYPE カラム値は BASE TABLE（BigQuery のテーブル） / VIEW（BigQuery のビュー） / EXTERNAL（外部データソースを参照するテーブル） のいずれかになります。 VIEW がある場合は VIEW の情報も一緒に取得できます。
続いて、TABLE_OPTIONS からテーブルの詳細情報を取得してみます。
データセットのオプション情報と同様、以下の項目が取得可能です。
- TABLE_CATALOG：プロジェクト名
- TABLE_SCHEMA：データセット名
- TABLE_NAME：テーブル名
- OPTION_NAME：オプション名
- OPTION_TYPE：オプション値のデータ型
- OPTION_VALUE：オプション値
テーブルのオプションの種類は、以下の７つです。
- partition_expiration_days：全てのパーティションの有効期間
- expiration_timestamp：有効期間
- kms_key_name：KMS キー名
- friendly_name：わかりやすいテーブル名
- description：テーブルの説明
- labels：ラベル
- require_partition_filter：パーティションフィルタ要否
データセットでも指定できるオプションに加えて、パーティションテーブルに関するオプションと、KMS を使用する場合の鍵情報が取得できました。
COLUMNS、COLUMN_FIELD_PATHS
COLUMNS View からテーブルを指定してカラム情報を取得してみます。
以下の項目が取得できました。
- TABLE_CATALOG：プロジェクト名
- TABLE_SCHEMA：データセット名
- TABLE_NAME：テーブル名
- COLUMN_NAME：カラム名
- ORDINAL_POSITION：カラム順
- IS_NULLABLE：NULL 値許可かどうか
- DATA_TYPE：データ型
- IS_HIDDEN/ IS_SYSTEM_DEFINED：疑似列かどうか
- IS_PARTITIONING_COLUMN：パーティショニングカラムかどうか
- CLUSTERING_ORDINAL_POSITION：クラスタリングカラム順
View にある IS_GENERATED、GENERATION_EXPRESSION、IS_STORED、IS_UPDATABLE のカラム値は今のところ固定値で、こちらも今後の拡張用項目のようです。
IS_HIDDEN と IS_SYSTEM_DEFINED の項目は、取り込み時間パーティション分割テーブルの場合に _PARTITIONTIME または _PARTITIONTIME の疑似列かどうかの情報です。
BigQuery では、カラムのデータ型に RECORD、モードで REPEATED を指定することにより、ネストされたカラムを持つテーブルを定義することができます。 例えばネストがある JSON フォーマットのデータをロードする場合などに便利です。
このネストされた列を持つテーブルのカラム情報を COLUMNS View で取得しようとすると、親階層のカラムの情報しか取得できません。
COLUMN_FIELD_PATHS View を参照すると、子階層のカラムの情報も取得できます。
- TABLE_CATALOG：プロジェクト名
- TABLE_SCHEMA：データセット名
- TABLE_NAME：テーブル名
- COLUMN_NAME：カラム名
- FIELD_PATH：カラムパス
- DATA_TYPE：データ型
- DESCRIPTION：カラムの説明
が取得できました。
COLUMNS View で取得できる情報の中にはカラムの説明がなかったので、各カラムの論理名を取得したい場合はこちらの COLUMN_FIELD_PATHS を参照する必要がありそうです。
ビュー情報
View の情報は、INFORMATION_SCHEMA.VIEWS から取得できます。
- VIEWS：ビュー情報
以下の View 情報が取得できました。
- TABLE_CATALOG：プロジェクト名
- TABLE_SCHEMA：データセット名
- TABLE_NAME：ビュー名
- VIEW_DEFINITION：ビュー定義
- USE_STANDARD_SQL：ビューが標準 SQL で作成されたかどうか
CHECK_OPTION 項目値は、現状 NULL 固定とのことです。
ビュー定義を確認したい場合は、この INFORMATION_SCHEMA.VIEWS を参照すれば良さそうです。
ルーティン情報
以下の View で、UDF やストアドプロシージャの情報を取得できます。
- ROUTINES：UDF、ストアドプロシージャ情報
- ROUTINE_OPTIONS：UDF、ストアドプロシージャのオプション情報
- PARAMETERS：UDF、ストアドプロシージャのパラメータ情報
ROUTINES で、UDFとストアドプロシージャの情報を取得してみます。
以下の項目が取得できました。
- SPECIFIC_CATALOG / ROUTINE_CATALOG：プロジェクト名
- SPECIFIC_SCHEMA / ROUTINE_SCHEMA：データセット名
- SPECIFIC_NAME / ROUTINE_NAME：ルーティン名
- ROUTINE_TYPE：ルーティンのタイプ
- DATA_TYPE：ルーティンが返却するデータの型
- ROUTINE_BODY：ルーティン定義言語
- ROUTINE_DEFINITION：ルーティン定義
- EXTERNAL_LANGUAGE：定義言語（定義言語が SQL 以外の場合）
- IS_DETERMINISTIC：決定的関数かどうか
- CREATED：作成日時
- LAST_MODIFIED：最終更新日時
ROUTINE_TYPE では、FUNCTION（ UDF ）か PROCEDURE（ストアドプロシージャ）のどちらかが取得できます。
ROUTINE_BODY は、定義言語が SQL の場合は SQL、それ以外の場合は EXTERNAL が入り、 EXTERNAL の場合は EXTERNAL_LANGUAGE で定義言語が分かります。現在、BigQuery の UDF 定義言語は SQL か JavaScript の二択なので、EXTERNAL_LANGUAGE の値は JAVASCRIPT か NULL（ SQL 定義の場合）のどちらかになります。
SECURITY_TYPE という項目もありますが、今のところ NULL 固定とのことです。
ROUTINE_OPTIONS からオプション情報も取得してみます。
取得できる項目は、データセットやテーブルと同様です。
- SPECIFIC_CATALOG：プロジェクト名
- SPECIFIC_SCHEMA：データセット名
- SPECIFIC_NAME：ルーティン名
- OPTION_NAME：オプション名
- OPTION_TYPE：オプション値のデータ型
- OPTION_VALUE：オプション値
オプションの種類は以下です。
- description：ルーティンの説明
- library：UDF が JavaScript の場合に参照しているライブラリ
続いて PARAMETERS から、UDF、ストアドプロシージャのパラメータ情報を取得してみます。
以下の情報が取得できました。
- SPECIFIC_CATALOG：プロジェクト名
- SPECIFIC_SCHEMA：データセット名
- SPECIFIC_NAME：ルーティン名
- ORDINAL_POSITION：パラメータの順序
- PARAMETER_MODE：パラメータのモード
- IS_RESULT：戻り値かどうか
- PARAMETER_NAME：変数名
- DATA_TYPE：データ型
PARAMETER_MODE には、IN、OUT、INOUT、NULL のいずれかが入りますが、定義時に明示的に指定していない場合は NULL になるようです。
他に PARAMETER_DEFAULT と IS_AGGREGATE の項目もありますが、NULL 固定とのことです。
ジョブ情報
BigQuery では、データのロード、エクスポート、コピーなどの処理は、インタラクティブな SQL クエリではなく、ジョブとして非同期で実行されます。
以下の View で、現在実行中のジョブや実行済みジョブの過去180日間分の履歴を取得できます。
- JOBS_BY_USER：ユーザーの実行ジョブ情報
- JOBS_BY_PROJECT：プロジェクトの実行ジョブ情報
- JOBS_BY_ORGANIZATION：組織の実行ジョブ情報
JOBS_BY_PROJECT を参照し、プロジェクトで実行したジョブの情報を取得してみます。
以下の情報が取得できました。
- creation_time：ジョブ作成日時
- project_id：プロジェクト名
- project_number：プロジェクト番号
- user_email：ユーザーの e-mail アドレス
- job_id：ジョブID
- job_type：ジョブのタイプ
- statement_type：クエリタイプ
- start_time：ジョブ開始日時
- end_time：ジョブ終了日時
- query：SQL クエリ
- state：ジョブステータス
- reservation_id：予約ID
- total_bytes_processed：ジョブ処理データの合計サイズ
- total_slot_ms：ジョブスロットの処理時間
- error_result：エラー情報
- cache_hit：クエリ結果がキャッシュされたかどうか
- destination_table：結果格納先情報
- referenced_tables：ジョブ参照テーブル情報
- labels：ジョブのラベル
- timeline：クエリのタイムライン
- job_stages：クエリの実行状態
job_type では、QUERY, LOAD, EXTRACT, COPY, UNKNOWN のいずれかのジョブ種別が取得できます。
statement_type で、ジョブ種別が QUERY の場合に、ELECT, INSERT, UPDATE, DELETE どのクエリが実行したかの情報が取得できます。
error_result、destination_table、referenced_tables、labels、timeline、job_stages はネスト構造で、さらに詳細な情報が取得できます。
- error_result
- reason
- location
- debug_info
- message
- destination_table / referenced_tables
- project_id
- dataset_id
- table_id
- labels
- key
- value
timeline、job_stages の詳細項目は以下に記載があります。
この JOBS_BY_* View で、ジョブの実行結果の参照はもちろん、スロット利用率や処理データ量も取得できるので、パフォーマンスやコスト管理にも利用できそうです。
Reservations 情報
日本語ドキュメントに「予約」とあったのでスケジューリングクエリの情報かと思ったのですが、ワークロード管理のための Reservations に関する情報とのことです。
Reservations 情報に関する View は、以下の４つです。
- RESERVATION_CHANGES_BY_PROJECT：Reservations 情報
- RESERVATION_TIMELINE_BY_PROJECT：Reservations のタイムライン情報
- CAPACITY_COMMITMENT_CHANGES_BY_PROJECT：容量コミットメントの変更情報
- ASSIGNMENT_CHANGES_BY_PROJECT：割り当ての変更情報
Reservations 開発者の立場から参照することはあまりなさそうですが、Reservations 機能を使っている場合に管理用途で使用すると便利そうですね。
まとめ（所感）
ところどころで固定値のカラムがあり、今後のバージョンアップに期待です。（カラム名から今後どんな情報が追加されるのかある程度予想できますね。
個人的には、現状では INFORMATION_SCHEMA の View からテーブルのレコード数やサイズが確認できないのが残念でした。 テーブルサイズやレコード数は、メタテーブル（ __TABLES__ ）から取得できるので、必要な情報を参照するための View を自分で作成しておくのも良いかと思いました。
